/* $RCSFile: CrmDB.cs $
 * Apache license v2 2011 by Christian Surieux
 * $Source: ?/ADNCRM.Core/DAL/CrmDB.cs $
 * $log$
 * Revision 6 2010/08/06 19:56:28  christian.surieux
 *   Added Log to All cs files
 */

using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Configuration;
using System.Globalization;

using AddonNice.Security;
using AddonNice.Diagnostics;
using AddonNice.Helpers;
using AddonNice.Settings;

using Trace2    =   System.Diagnostics.Trace;

namespace AddonNice.Configuration
{

    public class DBCrmEntity
    {
        public int EntityId;
        public string SchemaName;
        public string EntityVersion;
        public string FriendlyName;
        public string InitClass;
        public string LoadAssembly;
        public string PackageName;
        public bool NeedCreateBuilder;
        public bool NeedExtendBuilder;
        public bool NeedUIBuilder;

        public const string AddonNicePackage    =   "Addon Nice";
                    
        public DBCrmEntity(SqlDataReader dr)
        {
            EntityId            =   (int)dr["EntityId"];
            SchemaName          =   (string)dr["SchemaName"];
            EntityVersion       =   (string)dr["EntityVersion"];
            FriendlyName        =   (string)dr["FriendlyName"];
            InitClass           =   (string)dr["InitClass"];
            LoadAssembly        =   (string)dr["LoadAssembly"];
            object ob           =   dr["PackageName"];
            if ( ob is DBNull || ( ob.ToString() == string.Empty ) )
                 PackageName    =   AddonNicePackage;
            else PackageName    =   (string)ob;

            NeedCreateBuilder   =   (bool)dr["NeedCreateBuilder"];
            NeedExtendBuilder   =   (bool)dr["NeedExtendBuilder"];
            NeedUIBuilder       =   (bool)dr["NeedUIBuilder"];
        }
    
        public DBCrmEntity(DataRow dr)
        {
            EntityId            =   (int)dr["EntityId"];
            SchemaName          =   (string)dr["SchemaName"];
            EntityVersion       =   (string)dr["EntityVersion"];
            FriendlyName        =   (string)dr["FriendlyName"];
            InitClass           =   (string)dr["InitClass"];
            LoadAssembly        =   (string)dr["LoadAssembly"];
            object ob           =   dr["PackageName"];
            if ( ob is DBNull || ( ob.ToString() == string.Empty ) )
                 PackageName    =   AddonNicePackage;
            else PackageName    =   (string)ob;

            NeedCreateBuilder   =   (bool)dr["NeedCreateBuilder"];
            NeedExtendBuilder   =   (bool)dr["NeedExtendBuilder"];
            NeedUIBuilder       =   (bool)dr["NeedUIBuilder"];
        }
    }


    public class DBCrmServer
    {
        public int CrmServerId  =   -1;
        public string FriendlyName;
        public int CrmVersion;
        public bool UsePreAuthenticate;
        public string Organization;
        public string DiscoServer;
        public string Server3;
	            
        public string ServiceAccount;
        public string ServicePassword;
        public string ServiceDomain;

        public string PrivAccount;
        public string PrivPassword;
        public string PrivDomain;

        public string DefaultAccount;
        public string DefaultPassword;
        public string DefaultDomain;
        public string AdminRoleName;

        public int AuthenticationType;
        public int DiscoServerPort;

        public int MainPortalID     =   -1;

        public DBCrmServer() 
        {}

        public void InitFromConfig()
        {
            CrmServerId         =   -1;
            CrmVersion          =   Config.CRMServiceVersion;
			Organization        =   Config.CRMOrganization;
            AuthenticationType  =   Config.CRMAuthenticationType;
            DiscoServer         =   Config.CRMDiscoServer;
            DiscoServerPort     =   Config.CRMDiscoserverPort;
			Server3             =   Config.CRMServer;
			ServiceAccount      =   Config.CRMServiceAccount;
			ServicePassword     =   Config.CRMServicePassword;
			ServiceDomain       =   Config.CRMServiceDomain;
            
            PrivAccount         =   Config.CRMPrivAccount;
            PrivPassword        =   Config.CRMPrivPassword;
            PrivDomain          =   Config.CRMPrivDomain;

            DefaultAccount      =   Config.CRMDefaultAccount;
            DefaultPassword     =   Config.CRMDefaultAccountPassword;
            DefaultDomain       =   Config.CRMDefaultAccountDomain;

            AdminRoleName       =   Config.CRMAdminRole;
            
            UsePreAuthenticate  =   Config.CRMUsePreAuthenticate;
        }

        public DBCrmServer(SqlDataReader dr)
        {
            CrmServerId         =   (int)dr["CrmServerId"];
            FriendlyName        =   (string)dr["Friendlyname"];
            CrmVersion          =   int.Parse(dr["CrmVersion"].ToString());
            UsePreAuthenticate  =   bool.Parse(dr["UsePreAuthenticate"].ToString());
            Organization        =   (string)dr["Organization"];
            DiscoServer         =   (string)dr["DiscoServer"];
            object ob           =   dr["Server3"];;
            if ( ob is DBNull )
                 Server3        =   string.Empty;
            else Server3        =   (string)ob;
            
            ServiceAccount      =   (string)dr["ServiceAccount"];
            ServicePassword     =   (string)dr["ServicePassword"];
            ServiceDomain       =   (string)dr["ServiceDomain"];

            ob                  =   dr["PrivAccount"];
            if ( ob is DBNull )
                 PrivAccount    =   string.Empty;
            else PrivAccount    =   (string)ob;
            
            ob                  =   dr["PrivPassword"];
            if ( ob is DBNull )
                 PrivPassword   =   string.Empty;
            else PrivPassword   =   (string)ob;
            
            ob                  =   dr["PrivDomain"];
            if ( ob is DBNull )
                 PrivDomain     =   string.Empty;
            else PrivDomain     =   (string)ob;
            
            ob                  =   dr["MainPortalID"];
            if ( ob is DBNull )
                 MainPortalID   =   -1;
            else MainPortalID   =   (int)ob;
            
            DefaultAccount      =   (string)dr["DefaultAccount"];
            DefaultPassword     =   (string)dr["DefaultAccountPassword"];
            DefaultDomain       =   (string)dr["DefaultAccountDomain"];
            AdminRoleName       =   (string)dr["AdminRoleName"];

            AuthenticationType  =   int.Parse(dr["AuthenticationType"].ToString());
	        DiscoServerPort     =   int.Parse(dr["DiscoServerPort"].ToString());
        }

        public DBCrmServer(DataRow dr)
        {
            CrmServerId         =   (int)dr["CrmServerId"];
            FriendlyName        =   (string)dr["Friendlyname"];
            CrmVersion          =   int.Parse(dr["CrmVersion"].ToString());
            UsePreAuthenticate  =   bool.Parse(dr["UsePreAuthenticate"].ToString());
            Organization        =   (string)dr["Organization"];
            DiscoServer         =   (string)dr["DiscoServer"];
            object ob           =   dr["Server3"];;
            if ( ob is DBNull )
                 Server3        =   string.Empty;
            else Server3        =   (string)ob;
            
            ServiceAccount      =   (string)dr["ServiceAccount"];
            ServicePassword     =   (string)dr["ServicePassword"];
            ServiceDomain       =   (string)dr["ServiceDomain"];

            ob                  =   dr["PrivAccount"];
            if ( ob is DBNull )
                 PrivAccount    =   string.Empty;
            else PrivAccount    =   (string)ob;
            ob                  =   dr["PrivPassword"];
            if ( ob is DBNull )
                 PrivPassword   =   string.Empty;
            else PrivPassword   =   (string)ob;
            ob                  =   dr["PrivDomain"];
            if ( ob is DBNull )
                 PrivDomain     =   string.Empty;
            else PrivDomain     =   (string)ob;

            ob                  =   dr["MainPortalID"];
            if ( ob is DBNull )
                 MainPortalID   =   -1;
            else MainPortalID   =   (int)ob;

            DefaultAccount      =   (string)dr["DefaultAccount"];
            DefaultPassword     =   (string)dr["DefaultAccountPassword"];
            DefaultDomain       =   (string)dr["DefaultAccountDomain"];
            AdminRoleName       =   (string)dr["AdminRoleName"];

            AuthenticationType  =   int.Parse(dr["AuthenticationType"].ToString());
	        DiscoServerPort     =   int.Parse(dr["DiscoServerPort"].ToString());
        }
    }



    /// <summary>
    /// Class that encapsulates all data logic necessary to add/query/delete
    /// Portals within the Portal database.
    /// 
    /// </summary>
    public class CrmDB :	DBDataStore
    {
        /*
    CREATE TABLE [dbo].[adn_CrmEntity] (
	EntityId [int] IDENTITY (0, 1) NOT NULL ,
	SchemaName [nvarchar] (128) NOT NULL,
	EntityVersion [nvarchar] (16) NOT NULL,
	FriendlyName [nvarchar] (128) NULL,
    PackageName [nvarchar] (50),
	NeedCreateBuilder [bit] NOT NULL,
	NeedExtendBuilder [bit] NOT NULL,
    
CREATE TABLE [dbo].[adn_CrmServer] (
	CrmServerID int IDENTITY (0, 1) NOT NULL ,
	FriendlyName [nvarchar] (128),
	Version [int] NOT NULL,
    UsePreAuthenticate [bit] NOT NULL,
	Organization [nvarchar] (256) NOT NULL,
	AuthenticationType [int] NOT NULL,
	DiscoServer [nvarchar] (256) NOT NULL,
	DiscoServerPort [int] NOT NULL,
	Server3 [nvarchar] (256) NULL,
	ServiceAccount [nvarchar] (128) NOT NULL,
	ServicePassword [nvarchar] (128) NOT NULL,
	ServiceDomain [nvarchar] (128) NOT NULL,
	PrivAccount [nvarchar] (128) NOT NULL,
	PrivPassword [nvarchar] (128) NOT NULL,
	PrivDomain [nvarchar] (128) NOT NULL,
	DefaultAccount [nvarchar] (128) NOT NULL,
	DefaultAccountPassword [nvarchar] (128) NOT NULL,
	DefaultAccountDomain [nvarchar] (128) NOT NULL,
	AdminRoleName [nvarchar] (128) NOT NULL,
         
         */

        public CrmDB()
        {}
	
        #region Crm Entity

        public bool ExistCrmEntity(string SchemaName,string EntityVersion) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_ExistCrmEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterSchemaName	=   new SqlParameter("@SchemaName", SqlDbType.NVarChar, 128);
            parameterSchemaName.Value			=   SchemaName;
            myCommand.Parameters.Add(parameterSchemaName);

            SqlParameter parameterEntityVersion	=   new SqlParameter("@EntityVersion", SqlDbType.NVarChar, 16);
            parameterEntityVersion.Value		=   EntityVersion;
            myCommand.Parameters.Add(parameterEntityVersion);
            
            SqlParameter parameterFound	        =   new SqlParameter("@Found", SqlDbType.Bit);
            parameterFound.Direction            =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterFound);
            
            bool ret                            =   false;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                             =   (bool)parameterFound.Value;
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB ExistCrmEntity SchemaName: {0}, ex: {1}",SchemaName,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
		}

        public int ExistCrmEntityByCrmServer(int CrmServerId,ref int CountOther) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_ExistCrmEntityByCrmServer", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerId	=   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);
            // Count of entities already connected to this crm server
            SqlParameter parameterCountExist	=   new SqlParameter("@CountExist", SqlDbType.Int);
            parameterCountExist.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountExist);
            
            // Count of entities not already attached to this server
            SqlParameter parameterCountOther	=   new SqlParameter("@CountOther", SqlDbType.Int);
            parameterCountOther.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountOther);

            int ret                             =   0;
            CountOther                          =   0;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                             =   (int)parameterCountExist.Value;
                CountOther                      =   (int)parameterCountOther.Value;

			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB ExistCrmEntityByCrmServer CrmServerId: {0}, ex: {1}",CrmServerId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
		}

        public SqlDataReader GetCrmEntity(int EntityID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmEntity CrmEntityD: {0}",EntityID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmEntity", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmEntity CrmEntityD: {0}, ex: {1}",EntityID, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }
        
        public SqlDataReader GetCrmEntityBySchemaName(string SchemaName,string EntityVersion) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmEntityBySchemaName SchemaName: {0}",SchemaName),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmEntityBySchemaName", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterSchemaName	=   new SqlParameter("@SchemaName", SqlDbType.NVarChar,128);
            parameterSchemaName.Value			=   SchemaName;
            myCommand.Parameters.Add(parameterSchemaName);
            
            SqlParameter parameterEntityVersion	=   new SqlParameter("@EntityVersion", SqlDbType.NVarChar,16);
            parameterEntityVersion.Value			=   EntityVersion;
            myCommand.Parameters.Add(parameterEntityVersion);

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmEntityBySchemaName SchemaName: {0}, ex: {1}",SchemaName, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DBCrmEntity GetCrmEntityBySchemaNameObj(string SchemaName,string EntityVersion) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmEntityBySchemaNameObj SchemaName: {0}",SchemaName),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmEntityBySchemaName", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterSchemaName	=   new SqlParameter("@SchemaName", SqlDbType.NVarChar,128);
            parameterSchemaName.Value			=   SchemaName;
            myCommand.Parameters.Add(parameterSchemaName);
            
            SqlParameter parameterEntityVersion	=   new SqlParameter("@EntityVersion", SqlDbType.NVarChar,16);
            parameterEntityVersion.Value			=   EntityVersion;
            myCommand.Parameters.Add(parameterEntityVersion);
            
            DBCrmEntity result			        =	null;
			SqlDataReader dr			        =	null;
			try
			{
				myConnection.Open();
				dr                              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
                if ( dr.Read() )
                    result                      =   new DBCrmEntity(dr);
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmEntityBySchemaNameObj SchemaName: {0}, ex: {1}",SchemaName, ex),DBTraceSwitch.Sw.Info);
				throw;
            }
            finally
            {
				if ( dr != null && !dr.IsClosed )
					dr.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return result;
        }


        public DBCrmEntity GetCrmEntityObj(int EntityID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmEntityObj CrmEntityD: {0}",EntityID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmEntity", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
            DBCrmEntity result			        =	null;
			SqlDataReader dr			        =	null;
			try
			{
				myConnection.Open();
				dr                              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
                if ( dr.Read() )
                    result                      =   new DBCrmEntity(dr);
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmEntityObj CrmEntityD: {0}, ex: {1}",EntityID, ex),DBTraceSwitch.Sw.Info);
				throw;
            }
            finally
            {
				if ( dr != null && !dr.IsClosed )
					dr.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return result;
        }

        public SqlDataReader GetAllCrmEntity() 
		{
			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetAllCrmEntity", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmEntity GetAllCrmEntity ex: {0}",ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DataTable GetAllCrmEntityTb() 
		{
            SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetAllCrmEntity", myConnection);
			myAdapt.SelectCommand.CommandType	=   CommandType.StoredProcedure;

			DataTable Tb                        =   new DataTable();
            try
            {
                myAdapt.Fill(Tb);
            }
            catch ( Exception ex )
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine(string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetAllCrmEntityTb ex: {0}",ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
            }
            return Tb;
        }

        public DataTable GetAllCrmEntityPackagesTb() 
		{
            SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetAllCrmEntityPackages", myConnection);
			myAdapt.SelectCommand.CommandType	=   CommandType.StoredProcedure;

			DataTable Tb                        =   new DataTable();
            try
            {
                myAdapt.Fill(Tb);
            }
            catch ( Exception ex )
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine(string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetAllCrmEntityPackages ex: {0}",ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
            }
            return Tb;
        }

        public int AddCrmEntity(string SchemaName,string EntityVersion,string FriendlyName,string InitClass,string LoadAssembly,string PackageName,bool NeedCreateBuilder,bool NeedUpdateBuilder,bool NeedUIBuilder) 
        {
            SqlCommand myCommand				        =   new SqlCommand("dbo.adn_AddCrmEntity", myConnection);
            myCommand.CommandType				        =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	            =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Direction                 =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterEntityID);

            SqlParameter parameterSchemaName	        =   new SqlParameter("@SchemaName", SqlDbType.NVarChar, 128);
            parameterSchemaName.Value			        =   SchemaName;
            myCommand.Parameters.Add(parameterSchemaName);

            SqlParameter parameterEntityVersion	        =   new SqlParameter("@EntityVersion", SqlDbType.NVarChar, 16);
            parameterEntityVersion.Value		        =   EntityVersion;
            myCommand.Parameters.Add(parameterEntityVersion);

            SqlParameter parameterFriendlyName	        =   new SqlParameter("@FriendlyName", SqlDbType.NVarChar, 128);
            parameterFriendlyName.Value			        =   FriendlyName;
            myCommand.Parameters.Add(parameterFriendlyName);
            
            SqlParameter parameterInitClass	            =   new SqlParameter("@InitClass", SqlDbType.NVarChar, 128);
            parameterInitClass.Value			        =   InitClass;
            myCommand.Parameters.Add(parameterInitClass);
            
            SqlParameter parameterLoadAssembly	        =   new SqlParameter("@LoadAssembly", SqlDbType.NVarChar, 128);
            parameterLoadAssembly.Value			        =   LoadAssembly;
            myCommand.Parameters.Add(parameterLoadAssembly);

            SqlParameter parameterPackageName	        =   new SqlParameter("@PackageName", SqlDbType.NVarChar, 50);
            parameterPackageName.Value			        =   PackageName;
            myCommand.Parameters.Add(parameterPackageName);

            SqlParameter parameterNeedCreateBuilder	    =   new SqlParameter("@NeedCreateBuilder", SqlDbType.Bit);
            parameterNeedCreateBuilder.Value			=   NeedCreateBuilder;
            myCommand.Parameters.Add(parameterNeedCreateBuilder);

            SqlParameter parameterNeedExtendBuilder	    =   new SqlParameter("@NeedExtendBuilder", SqlDbType.Bit);
            parameterNeedExtendBuilder.Value			=   NeedUpdateBuilder;
            myCommand.Parameters.Add(parameterNeedExtendBuilder);

            SqlParameter parameterNeedNeedUIBuilder	    =   new SqlParameter("@NeedUIBuilder", SqlDbType.Bit);
            parameterNeedNeedUIBuilder.Value			=   NeedUIBuilder;
            myCommand.Parameters.Add(parameterNeedNeedUIBuilder);

            int ret                                     =   -1;
            try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                                     =   (int)parameterEntityID.Value;
			}
			catch(Exception exp)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB AddCrmEntity SchemaName: {0}, ex: {1}",SchemaName,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
        }		
        
        public void UpdateCrmEntity(int EntityID,string SchemaName,string EntityVersion,string FriendlyName,string InitClass,string LoadAssembly,string PackageName,bool NeedCreateBuilder,bool NeedUpdateBuilder,bool NeedUIBuilder) 
        {
            SqlCommand myCommand			            =   new SqlCommand("dbo.adn_UpdateCrmEntity", myConnection);
            myCommand.CommandType			            =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	            =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			            =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);

            SqlParameter parameterSchemaName	        =   new SqlParameter("@SchemaName", SqlDbType.NVarChar, 128);
            parameterSchemaName.Value			        =   SchemaName;
            myCommand.Parameters.Add(parameterSchemaName);

            SqlParameter parameterEntityVersion	        =   new SqlParameter("@EntityVersion", SqlDbType.NVarChar, 16);
            parameterEntityVersion.Value		        =   EntityVersion;
            myCommand.Parameters.Add(parameterEntityVersion);

            SqlParameter parameterFriendlyName	        =   new SqlParameter("@FriendlyName", SqlDbType.NVarChar, 128);
            parameterFriendlyName.Value			        =   FriendlyName;
            myCommand.Parameters.Add(parameterFriendlyName);

            SqlParameter parameterInitClass	            =   new SqlParameter("@InitClass", SqlDbType.NVarChar, 128);
            parameterInitClass.Value			        =   InitClass;
            myCommand.Parameters.Add(parameterInitClass);
            
            SqlParameter parameterLoadAssembly	        =   new SqlParameter("@LoadAssembly", SqlDbType.NVarChar, 128);
            parameterLoadAssembly.Value			        =   LoadAssembly;
            myCommand.Parameters.Add(parameterLoadAssembly);

            SqlParameter parameterPackageName	        =   new SqlParameter("@PackageName", SqlDbType.NVarChar, 50);
            parameterPackageName.Value			        =   PackageName;
            myCommand.Parameters.Add(parameterPackageName);

            SqlParameter parameterNeedCreateBuilder	    =   new SqlParameter("@NeedCreateBuilder", SqlDbType.Bit);
            parameterNeedCreateBuilder.Value			=   NeedCreateBuilder;
            myCommand.Parameters.Add(parameterNeedCreateBuilder);

            SqlParameter parameterNeedExtendBuilder	    =   new SqlParameter("@NeedExtendBuilder", SqlDbType.Bit);
            parameterNeedExtendBuilder.Value			=   NeedUpdateBuilder;
            myCommand.Parameters.Add(parameterNeedExtendBuilder);

            SqlParameter parameterNeedNeedUIBuilder	    =   new SqlParameter("@NeedUIBuilder", SqlDbType.Bit);
            parameterNeedNeedUIBuilder.Value			=   NeedUIBuilder;
            myCommand.Parameters.Add(parameterNeedNeedUIBuilder);

            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB UpdateCrmEntity EntityID: {0}, ex: {1}",EntityID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
        
        public void DeleteCrmEntity(int EntityID) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_DeleteCrmEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB DeleteCrmEntity EntityID: {0}, ex: {1}",EntityID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
        
        #endregion Crm Entity

        #region Entity ModuleGeneralDefinition

        public SqlDataReader GetEntitiesByModule(Guid ModuleDefId) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetEntitiesByModule ModuleDefId: {0}",ModuleDefId),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetEntitiesByModule", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterModuleDefId	=   new SqlParameter("@ModuleDefId", SqlDbType.Int);
            parameterModuleDefId.Value			=   ModuleDefId;
            myCommand.Parameters.Add(parameterModuleDefId);
            

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetEntitiesByModule ModuleDefId: {0}, ex: {1}",ModuleDefId, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public SqlDataReader GetModulesByEntity(int EntityID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetModulesByEntity EntityID: {0}",EntityID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetModulesByEntity", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetModulesByEntity EntityID: {0}, ex: {1}",EntityID, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DataTable GetModulesByEntityTb(int EntityID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetModulesByEntityTb EntityID: {0}",EntityID),DBTraceSwitch.Sw.Info);

			SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetModulesByEntity", myConnection);
			myAdapt.SelectCommand.CommandType	=   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myAdapt.SelectCommand.Parameters.Add(parameterEntityID);
            

			DataTable Tb                        =   new DataTable();
            try
            {
                myAdapt.Fill(Tb);
            }
            catch ( Exception ex )
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine(string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetModulesByEntityTb CrmServerId: {0}, ex: {1}",EntityID,ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
            }
            return Tb;
        }

        public void AddModuleEntity(int EntityID,Guid ModuleDefId) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_AddModuleEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
            SqlParameter parameterModuleDefId	=   new SqlParameter("@ModuleDefId", SqlDbType.UniqueIdentifier);
            parameterModuleDefId.Value			=   ModuleDefId;
            myCommand.Parameters.Add(parameterModuleDefId);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB AddModuleEntity EntityID: {0}, ModuleDefId: {1}, ex: {2}",EntityID,ModuleDefId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        public void DeleteModuleEntity(int EntityID,Guid ModuleDefId) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_DeleteModuleEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	=   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			=   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
            SqlParameter parameterModuleDefId	=   new SqlParameter("@ModuleDefId", SqlDbType.UniqueIdentifier);
            parameterModuleDefId.Value			=   ModuleDefId;
            myCommand.Parameters.Add(parameterModuleDefId);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB DeleteModuleEntity EntityID: {0}, ModuleDefId: {1}, ex: {2}",EntityID,ModuleDefId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        public int ExistModulesByCrmEntity(int EntityId,ref int CountOther) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_ExistModulesByCrmEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerId	=   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterCrmServerId.Value			=   EntityId;
            myCommand.Parameters.Add(parameterCrmServerId);
            
            // Count of entities already connected to this crm server
            SqlParameter parameterCountExist	=   new SqlParameter("@CountExist", SqlDbType.Int);
            parameterCountExist.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountExist);
            
            // Count of entities not already attached to this server
            SqlParameter parameterCountOther	=   new SqlParameter("@CountOther", SqlDbType.Int);
            parameterCountOther.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountOther);

            int ret                             =   0;
            CountOther                          =   0;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                             =   (int)parameterCountExist.Value;
                CountOther                      =   (int)parameterCountOther.Value;

			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB ExistModulesByCrmEntity EntityId: {0}, ex: {1}",EntityId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
		}

        #endregion Entity ModuleGeneralDefinition

        #region Entity CrmServer

        public bool ExistCrmServerEntity(int EntityID,int CrmServerId) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_ExistCrmServerEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
            SqlParameter parameterCrmServerId	=   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);
            
            SqlParameter parameterFound	        =   new SqlParameter("@Found", SqlDbType.Bit);
            parameterFound.Direction            =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterFound);
            
            bool ret                            =   false;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                             =   (bool)parameterFound.Value;
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB ExistCrmServerEntity EntityID: {0}, CrmServerId: {1}, ex: {2}",EntityID,CrmServerId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
		}
        
        public int ExistCrmServerByCrmEntity(int EntityId,ref int CountOther) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_ExistCrmServerByCrmEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerId	=   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterCrmServerId.Value			=   EntityId;
            myCommand.Parameters.Add(parameterCrmServerId);
            
            // Count of entities already connected to this crm server
            SqlParameter parameterCountExist	=   new SqlParameter("@CountExist", SqlDbType.Int);
            parameterCountExist.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountExist);
            
            // Count of entities not already attached to this server
            SqlParameter parameterCountOther	=   new SqlParameter("@CountOther", SqlDbType.Int);
            parameterCountOther.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountOther);

            int ret                             =   0;
            CountOther                          =   0;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                             =   (int)parameterCountExist.Value;
                CountOther                      =   (int)parameterCountOther.Value;

			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB ExistCrmServerByCrmEntity EntityId: {0}, ex: {1}",EntityId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
		}

        public SqlDataReader GetCrmServerEntity(int CrmServerId,int EntityID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmServerEntity CrmServerId: {0}",CrmServerId),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmServerEntity", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerId	=   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);
            
            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmServerEntity CrmServerId: {0}, EntityID: {1}, ex: {2}",CrmServerId,EntityID,ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public SqlDataReader GetEntitiesByCrmServer(int CrmServerId) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetEntitiesByCrmServer CrmServerId: {0}",CrmServerId),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetEntitiesByCrmServer", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerId	=   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);
            

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetEntitiesByCrmServer CrmServerId: {0}, ex: {1}",CrmServerId, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DataTable GetEntitiesByCrmServerTb(int CrmServerId)
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetEntitiesByCrmServerTb CrmServerId: {0}",CrmServerId),DBTraceSwitch.Sw.Info);

			SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetEntitiesByCrmServer", myConnection);
			myAdapt.SelectCommand.CommandType	=   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerId	=   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			=   CrmServerId;
            myAdapt.SelectCommand.Parameters.Add(parameterCrmServerId);
            

			DataTable Tb                        =   new DataTable();
            try
            {
                myAdapt.Fill(Tb);
            }
            catch ( Exception ex )
            {
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine(string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetEntitiesByCrmServerTb CrmServerId: {0}, ex: {1}",CrmServerId,ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
            }
            return Tb;
        }

        public SqlDataReader GetCrmServersByEntity(int EntityID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmServersByEntity EntityID: {0}",EntityID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmServersByEntity", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	=   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			=   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmServersByEntity EntityID: {0}, ex: {1}",EntityID, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DataTable GetCrmServersByEntityTb(int EntityID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmServersByEntity EntityID: {0}",EntityID),DBTraceSwitch.Sw.Info);

			SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetCrmServersByEntity", myConnection);
			myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myAdapt.SelectCommand.Parameters.Add(parameterEntityID);
            
			DataTable Tb			            =	new DataTable();
			try
			{
				myAdapt.Fill(Tb); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmServersByEntityTb EntityID, {0}, ex: {1}",EntityID,ex),DBTraceSwitch.Sw.Info);
				throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return Tb;
        }

        public void AddCrmServerEntity(int EntityID,int CrmServerId,string InstalledVersion) 
        {
            SqlCommand myCommand			        =   new SqlCommand("dbo.adn_AddCrmServerEntity", myConnection);
            myCommand.CommandType			        =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	        =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			        =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
            SqlParameter parameterCrmServerId	    =   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			    =   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);

            SqlParameter parameterInstalledVersion  =   new SqlParameter("@InstalledVersion", SqlDbType.NVarChar,16);
            parameterInstalledVersion.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterInstalledVersion);
                    
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB AddCrmServerEntity EntityID: {0}, CrmServerId: {1}, ex: {2}",EntityID,CrmServerId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        public void UpdateCrmServerEntity(int EntityID,int CrmServerId,string InstalledVersion) 
        {
            SqlCommand myCommand			        =   new SqlCommand("dbo.adn_UpdateCrmServerEntity", myConnection);
            myCommand.CommandType			        =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	        =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			        =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
            SqlParameter parameterCrmServerId	    =   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			    =   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);

            SqlParameter parameterInstalledVersion  =   new SqlParameter("@InstalledVersion", SqlDbType.NVarChar,16);
            parameterInstalledVersion.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterInstalledVersion);
                    
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB UpdateCrmServerEntity EntityID: {0}, CrmServerId: {1}, ex: {2}",EntityID,CrmServerId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        public void DeleteCrmServerEntity(int EntityID,int CrmServerId) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_DeleteCrmServerEntity", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterEntityID	    =   new SqlParameter("@EntityID", SqlDbType.Int);
            parameterEntityID.Value			    =   EntityID;
            myCommand.Parameters.Add(parameterEntityID);
            
            SqlParameter parameterCrmServerId	=   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB DeleteCrmServerEntity EntityID: {0}, CrmServerId: {1}, ex: {2}",EntityID,CrmServerId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        #endregion Entity Portal

        #region Crm Server

        public SqlDataReader GetCrmServer(int CrmServerID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmServer GetCrmServerD: {0}",CrmServerID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmServer", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);
            

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmServer CrmServerD: {0}, ex: {1}",CrmServerID, ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DBCrmServer GetCrmServerObj(int CrmServerID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmServer GetCrmServerD: {0}",CrmServerID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmServer", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);
            
            
			DBCrmServer result			        =	null;
            SqlDataReader dr                    =   null;
			try
			{
				myConnection.Open();
				dr                              =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
                if ( dr.Read() )
                    result                      =   new DBCrmServer(dr);
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmServer CrmServerD: {0}, ex: {1}",CrmServerID, ex),DBTraceSwitch.Sw.Info);
				throw;
			}
            finally
			{
				if ( dr != null && !dr.IsClosed )
					dr.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
            }
            return result;
        }

        public SqlDataReader GetAllCrmServer() 
		{
			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetAllCrmServer", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;


			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetAllCrmServer ex: {0}",ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public DataTable GetAllCrmServerTb() 
		{
			SqlDataAdapter myAdapt			    =   new SqlDataAdapter("dbo.adn_GetAllCrmServer", myConnection);
			myAdapt.SelectCommand.CommandType   =   CommandType.StoredProcedure;

			DataTable Tb			            =	new DataTable();
			try
			{
				myAdapt.Fill(Tb); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetAllCrmServerTb ex: {0}",ex),DBTraceSwitch.Sw.Info);
				throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return Tb;
        }

        public int AddCrmServer(string FriendlyName,int CrmVersion,
                                    bool UsePreAuthenticate,
	                                string Organization,
	                                int AuthenticationType,
	                                string DiscoServer,
	                                int DiscoServerPort,
	                                string Server3,
	                                string ServiceAccount,
	                                string ServicePassword,
	                                string ServiceDomain,
	                                string PrivAccount,
	                                string PrivPassword,
	                                string PrivDomain,
	                                string DefaultAccount,
	                                string DefaultAccountPassword,
	                                string DefaultAccountDomain,
	                                string AdminRoleName)

        {
            SqlCommand myCommand				        =   new SqlCommand("dbo.adn_AddCrmServer", myConnection);
            myCommand.CommandType				        =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	        =   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Direction              =   ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCrmServerID);

            SqlParameter parameterFriendlyName	        =   new SqlParameter("@FriendlyName", SqlDbType.NVarChar, 128);
            parameterFriendlyName.Value			        =   FriendlyName;
            myCommand.Parameters.Add(parameterFriendlyName);

            SqlParameter parameterCrmVersion	        =   new SqlParameter("@CrmVersion", SqlDbType.Int);
            parameterCrmVersion.Value			        =   CrmVersion;
            myCommand.Parameters.Add(parameterCrmVersion);

            SqlParameter parameterAuthenticationType	=   new SqlParameter("@AuthenticationType", SqlDbType.Int);
            parameterAuthenticationType.Value			=   AuthenticationType;
            myCommand.Parameters.Add(parameterAuthenticationType);

            SqlParameter parameterUsePreAuthenticate	=   new SqlParameter("@UsePreAuthenticate", SqlDbType.Bit);
            parameterUsePreAuthenticate.Value			=   UsePreAuthenticate;
            myCommand.Parameters.Add(parameterUsePreAuthenticate);

            SqlParameter parameterOrganization	        =   new SqlParameter("@Organization", SqlDbType.NVarChar, 256);
            parameterOrganization.Value			        =   Organization;
            myCommand.Parameters.Add(parameterOrganization);

            SqlParameter parameterDiscoServer	        =   new SqlParameter("@DiscoServer", SqlDbType.NVarChar, 256);
            parameterDiscoServer.Value			        =   DiscoServer;
            myCommand.Parameters.Add(parameterDiscoServer);

            SqlParameter parameterDiscoServerPort	    =   new SqlParameter("@DiscoServerPort", SqlDbType.Int);
            parameterDiscoServerPort.Value			    =   DiscoServerPort;
            myCommand.Parameters.Add(parameterDiscoServerPort);

            SqlParameter parameterServer3	            =   new SqlParameter("@Server3", SqlDbType.NVarChar, 256);
            parameterServer3.Value			            =   ( Server3 == null ) ? string.Empty:Server3;
            myCommand.Parameters.Add(parameterServer3);

            SqlParameter parameterServiceAccount	    =   new SqlParameter("@ServiceAccount", SqlDbType.NVarChar, 128);
            parameterServiceAccount.Value			    =   ServiceAccount;
            myCommand.Parameters.Add(parameterServiceAccount);

            SqlParameter parameterServicePassword	    =   new SqlParameter("@ServicePassword", SqlDbType.NVarChar, 128);
            parameterServicePassword.Value			    =   ServicePassword;
            myCommand.Parameters.Add(parameterServicePassword);

            SqlParameter parameterServiceDomain	        =   new SqlParameter("@ServiceDomain", SqlDbType.NVarChar, 128);
            parameterServiceDomain.Value			    =   ServiceDomain;
            myCommand.Parameters.Add(parameterServiceDomain);

            
            SqlParameter parameterPrivAccount	        =   new SqlParameter("@PrivAccount", SqlDbType.NVarChar, 128);
            parameterPrivAccount.Value			        =   PrivAccount;
            myCommand.Parameters.Add(parameterPrivAccount);

            SqlParameter parameterPrivPassword	        =   new SqlParameter("@PrivPassword", SqlDbType.NVarChar, 128);
            parameterPrivPassword.Value			        =   PrivPassword;
            myCommand.Parameters.Add(parameterPrivPassword);

            SqlParameter parameterPrivDomain	        =   new SqlParameter("@PrivDomain", SqlDbType.NVarChar, 128);
            parameterPrivDomain.Value			        =   PrivDomain;
            myCommand.Parameters.Add(parameterPrivDomain);

            SqlParameter parameterDefaultAccount	    =   new SqlParameter("@DefaultAccount", SqlDbType.NVarChar, 128);
            parameterDefaultAccount.Value			    =   DefaultAccount;
            myCommand.Parameters.Add(parameterDefaultAccount);

            SqlParameter parameterDefaultAccountPassword=   new SqlParameter("@DefaultAccountPassword", SqlDbType.NVarChar, 128);
            parameterDefaultAccountPassword.Value		=   DefaultAccountPassword;
            myCommand.Parameters.Add(parameterDefaultAccountPassword);

            SqlParameter parameterDefaultAccountDomain	=   new SqlParameter("@DefaultAccountDomain", SqlDbType.NVarChar, 128);
            parameterDefaultAccountDomain.Value			=   DefaultAccountDomain;
            myCommand.Parameters.Add(parameterDefaultAccountDomain);

            SqlParameter parameterAdminRoleName	        =   new SqlParameter("@AdminRoleName", SqlDbType.NVarChar, 128);
            parameterAdminRoleName.Value			    =   AdminRoleName;
            myCommand.Parameters.Add(parameterAdminRoleName);

            int ret                                     =   -1;
            try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                                     =   (int)parameterCrmServerID.Value;
			}
			catch(Exception exp)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB AddCrmServer FriendlyName: {0}, ex: {1}",FriendlyName,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
        }		
        
        public void UpdateCrmServer(int CrmServerID,string FriendlyName,int CrmVersion,
                                    bool UsePreAuthenticate,
	                                string Organization,
	                                int AuthenticationType,
	                                string DiscoServer,
	                                int DiscoServerPort,
	                                string Server3,
	                                string ServiceAccount,
	                                string ServicePassword,
	                                string ServiceDomain,
	                                string PrivAccount,
	                                string PrivPassword,
	                                string PrivDomain,
	                                string DefaultAccount,
	                                string DefaultAccountPassword,
	                                string DefaultAccountDomain,
	                                string AdminRoleName)
        {
            SqlCommand myCommand			            =   new SqlCommand("dbo.adn_UpdateCrmServer", myConnection);
            myCommand.CommandType			            =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	        =   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			        =   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);
            
            SqlParameter parameterFriendlyName	        =   new SqlParameter("@FriendlyName", SqlDbType.NVarChar, 128);
            parameterFriendlyName.Value			        =   FriendlyName;
            myCommand.Parameters.Add(parameterFriendlyName);

            SqlParameter parameterCrmVersion	        =   new SqlParameter("@CrmVersion", SqlDbType.Int);
            parameterCrmVersion.Value			        =   CrmVersion;
            myCommand.Parameters.Add(parameterCrmVersion);

            SqlParameter parameterAuthenticationType	=   new SqlParameter("@AuthenticationType", SqlDbType.Int);
            parameterAuthenticationType.Value			=   AuthenticationType;
            myCommand.Parameters.Add(parameterAuthenticationType);

            SqlParameter parameterUsePreAuthenticate	=   new SqlParameter("@UsePreAuthenticate", SqlDbType.Bit);
            parameterUsePreAuthenticate.Value			=   UsePreAuthenticate;
            myCommand.Parameters.Add(parameterUsePreAuthenticate);

            SqlParameter parameterOrganization	        =   new SqlParameter("@Organization", SqlDbType.NVarChar, 256);
            parameterOrganization.Value			        =   Organization;
            myCommand.Parameters.Add(parameterOrganization);

            SqlParameter parameterDiscoServer	        =   new SqlParameter("@DiscoServer", SqlDbType.NVarChar, 256);
            parameterDiscoServer.Value			        =   DiscoServer;
            myCommand.Parameters.Add(parameterDiscoServer);

            SqlParameter parameterDiscoServerPort	    =   new SqlParameter("@DiscoServerPort", SqlDbType.Int);
            parameterDiscoServerPort.Value			    =   DiscoServerPort;
            myCommand.Parameters.Add(parameterDiscoServerPort);

            SqlParameter parameterServer3	            =   new SqlParameter("@Server3", SqlDbType.NVarChar, 256);
            parameterServer3.Value			            =   ( Server3 == null ) ? string.Empty:Server3;
            myCommand.Parameters.Add(parameterServer3);

            SqlParameter parameterServiceAccount	    =   new SqlParameter("@ServiceAccount", SqlDbType.NVarChar, 128);
            parameterServiceAccount.Value			    =   ServiceAccount;
            myCommand.Parameters.Add(parameterServiceAccount);

            SqlParameter parameterServicePassword	    =   new SqlParameter("@ServicePassword", SqlDbType.NVarChar, 128);
            parameterServicePassword.Value			    =   ServicePassword;
            myCommand.Parameters.Add(parameterServicePassword);

            SqlParameter parameterServiceDomain	        =   new SqlParameter("@ServiceDomain", SqlDbType.NVarChar, 128);
            parameterServiceDomain.Value			    =   ServiceDomain;
            myCommand.Parameters.Add(parameterServiceDomain);

            
            SqlParameter parameterPrivAccount	        =   new SqlParameter("@PrivAccount", SqlDbType.NVarChar, 128);
            parameterPrivAccount.Value			        =   PrivAccount;
            myCommand.Parameters.Add(parameterPrivAccount);

            SqlParameter parameterPrivPassword	        =   new SqlParameter("@PrivPassword", SqlDbType.NVarChar, 128);
            parameterPrivPassword.Value			        =   PrivPassword;
            myCommand.Parameters.Add(parameterPrivPassword);

            SqlParameter parameterPrivDomain	        =   new SqlParameter("@PrivDomain", SqlDbType.NVarChar, 128);
            parameterPrivDomain.Value			        =   PrivDomain;
            myCommand.Parameters.Add(parameterPrivDomain);

            SqlParameter parameterDefaultAccount	    =   new SqlParameter("@DefaultAccount", SqlDbType.NVarChar, 128);
            parameterDefaultAccount.Value			    =   DefaultAccount;
            myCommand.Parameters.Add(parameterDefaultAccount);

            SqlParameter parameterDefaultAccountPassword=   new SqlParameter("@DefaultAccountPassword", SqlDbType.NVarChar, 128);
            parameterDefaultAccountPassword.Value		=   DefaultAccountPassword;
            myCommand.Parameters.Add(parameterDefaultAccountPassword);

            SqlParameter parameterDefaultAccountDomain	=   new SqlParameter("@DefaultAccountDomain", SqlDbType.NVarChar, 128);
            parameterDefaultAccountDomain.Value			=   DefaultAccountDomain;
            myCommand.Parameters.Add(parameterDefaultAccountDomain);

            SqlParameter parameterAdminRoleName	        =   new SqlParameter("@AdminRoleName", SqlDbType.NVarChar, 128);
            parameterAdminRoleName.Value			    =   AdminRoleName;
            myCommand.Parameters.Add(parameterAdminRoleName);

			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB UpdateCrmServerID CrmServerID: {0}, ex: {1}",CrmServerID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
        
        /// <summary>
        /// Removes portal alias from database
        /// </summary>
        public void DeleteCrmServer(int CrmServerID) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_DeleteCrmServer", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB DeleteCrmServer CrmServerID: {0}, ex: {1}",CrmServerID,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}
        
        #endregion Crm Server

        #region Crm Server Portal

        public int GetFirstCrmServerByPortal(int PortalID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetFirstCrmServerByPortal PortalID: {0}",PortalID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetFirstCrmServerByPortal", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalID);

            SqlParameter parameterCrmServerID   =   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Direction      =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCrmServerID);

            int ret                             =   -1;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery(); 
                ret                             =   (int)parameterCrmServerID.Value;

			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetFirstCrmServerByPortal PortalID: {0}, ex: {1}",PortalID, ex),DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
        }
        

        public SqlDataReader GetCrmServerByPortal(int PortalID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmServerByPortal PortalID: {0}",PortalID),DBTraceSwitch.Sw.Info);

			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetCrmServerByPortal", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalID);

            SqlDataReader ret                   =   null;
			try
			{
				myConnection.Open();
				ret                             =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmServerByPortal PortalID: {0}, ex: {1}",PortalID, ex),DBTraceSwitch.Sw.Info);
				if ( ret != null )
                    ret.Close();
                if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
                throw;
            }
            return ret;
        }
        

        public DataTable GetCrmServerByPortalTb(int PortalID) 
		{
            if ( DBTraceSwitch.Sw.TraceVerbose )
                Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[V]CrmDB GetCrmServerByPortalTb PortalID: {0}",PortalID),DBTraceSwitch.Sw.Info);

			SqlDataAdapter  myAdapt			    =   new SqlDataAdapter("dbo.adn_GetCrmServerByPortal", myConnection);
			myAdapt.SelectCommand.CommandType	=   CommandType.StoredProcedure;

            AddPortalIDParameters(myAdapt.SelectCommand,PortalID);

			DataTable Tb			            =	new DataTable();
			try
			{
				myAdapt.Fill(Tb);
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetCrmServerByPortalTb ex: {0}",ex),DBTraceSwitch.Sw.Info);
				throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return Tb;
        }
        

        public SqlDataReader GetPortalsByCrmServer(int CrmServerID) 
		{
			SqlCommand myCommand			    =   new SqlCommand("dbo.adn_GetPortalsByCrmServer", myConnection);
			myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);

			SqlDataReader result			    =	null;
			try
			{
				myConnection.Open();
				result                          =   myCommand.ExecuteReader(CommandBehavior.CloseConnection); 
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetPortalsByCrmServer ex: {0}",ex),DBTraceSwitch.Sw.Info);
				if ( result != null && !result.IsClosed )
					result.Close();
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
				throw;
			}
            return result;
        }

        public int ExistPortalsByCrmServer(int CrmServerId,ref int CountOther) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_ExistPortalsByCrmServer", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerId	=   new SqlParameter("@CrmServerId", SqlDbType.Int);
            parameterCrmServerId.Value			=   CrmServerId;
            myCommand.Parameters.Add(parameterCrmServerId);
            
            // Count of entities already connected to this crm server
            SqlParameter parameterCountExist	=   new SqlParameter("@CountExist", SqlDbType.Int);
            parameterCountExist.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountExist);
            
            // Count of entities not already attached to this server
            SqlParameter parameterCountOther	=   new SqlParameter("@CountOther", SqlDbType.Int);
            parameterCountOther.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountOther);

            int ret                             =   0;
            CountOther                          =   0;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                             =   (int)parameterCountExist.Value;
                CountOther                      =   (int)parameterCountOther.Value;

			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB ExistPortalsByCrmServer CrmServerId: {0}, ex: {1}",CrmServerId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
		}

        public int ExistCrmServersByPortal(int PortalId,ref int CountOther) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_ExistCrmServersByPortal", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            AddPortalIDParameters(myCommand,PortalId);
            
            // Count of entities already connected to this crm server
            SqlParameter parameterCountExist	=   new SqlParameter("@CountExist", SqlDbType.Int);
            parameterCountExist.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountExist);
            
            // Count of entities not already attached to this server
            SqlParameter parameterCountOther	=   new SqlParameter("@CountOther", SqlDbType.Int);
            parameterCountOther.Direction       =    ParameterDirection.Output;
            myCommand.Parameters.Add(parameterCountOther);

            int ret                             =   0;
            CountOther                          =   0;
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
                ret                             =   (int)parameterCountExist.Value;
                CountOther                      =   (int)parameterCountOther.Value;

			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB ExistCrmServersByPortal PortalId: {0}, ex: {1}",PortalId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return ret;
		}

        public DataTable GetPortalsByCrmServerTb(int CrmServerID) 
		{
			SqlDataAdapter  myAdapt			    =   new SqlDataAdapter("dbo.adn_GetPortalsByCrmServer", myConnection);
			myAdapt.SelectCommand.CommandType			        =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myAdapt.SelectCommand.Parameters.Add(parameterCrmServerID);

			DataTable Tb			            =	new DataTable();
			try
			{
				myAdapt.Fill(Tb);
			}
			catch(Exception ex)
			{
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace2.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB GetPortalsByCrmServerTb ex: {0}",ex),DBTraceSwitch.Sw.Info);
				throw;
            }
            finally
            {
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
            return Tb;
        }
        
        public void UpdateCrmServerMainPortal(int CrmServerID,int PortalId) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_UpdateCrmServerMainPortal", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);
            
            
            AddPortalIDParameters(myCommand,PortalId);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB UpdateCrmServerMainPortal CrmServerID: {0}, PortalId: {1}, ex: {2}",CrmServerID,PortalId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        public void AddPortalCrmServer(int CrmServerID,int PortalId,bool IsMainPortal) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_AddPortalCrmServer", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);
            
            SqlParameter parameterIsMainPortal	=   new SqlParameter("@IsMainPortal", SqlDbType.Bit);
            parameterIsMainPortal.Value			=   IsMainPortal;
            myCommand.Parameters.Add(parameterIsMainPortal);
            
            AddPortalIDParameters(myCommand,PortalId);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB AddPortalCrmServer CrmServerID: {0}, PortalId: {1}, ex: {2}",CrmServerID,PortalId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        public void DeletePortalCrmServer(int CrmServerID,int PortalId) 
        {
            SqlCommand myCommand			    =   new SqlCommand("dbo.adn_DeletePortalCrmServer", myConnection);
            myCommand.CommandType			    =   CommandType.StoredProcedure;

            SqlParameter parameterCrmServerID	=   new SqlParameter("@CrmServerID", SqlDbType.Int);
            parameterCrmServerID.Value			=   CrmServerID;
            myCommand.Parameters.Add(parameterCrmServerID);
            
            
            AddPortalIDParameters(myCommand,PortalId);
            
			try
			{
				myConnection.Open();
				myCommand.ExecuteNonQuery();
			}
			catch(Exception exp)
			{
				if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine( string.Format(CultureInfo.InvariantCulture,"[E]CrmDB DeletePortalCrmServer CrmServerID: {0}, PortalId: {1}, ex: {2}",CrmServerID,PortalId,exp),DBTraceSwitch.Sw.Info);
				throw;
			}
			finally
			{
				if ( myConnection.State == System.Data.ConnectionState.Open )
					myConnection.Close();
			}
		}

        #endregion Crm Server Portal

        #region used by CrmInstaller
        
        public void GrantAccess(string login,string role)
        {
            SqlConnection myStaticConnection    =   DBDataStore.CreateStaticConnection();
            SqlCommand myCommand                =   new SqlCommand("dbo.adn_GrantAccess", myStaticConnection);
			myCommand.CommandType		        =   CommandType.StoredProcedure;
            
            SqlParameter parameterLogin	        =   new SqlParameter("@login", SqlDbType.NVarChar,256);
            parameterLogin.Value	            =   login;
            myCommand.Parameters.Add(parameterLogin);
	        
            SqlParameter parameterRole	        =   new SqlParameter("@role", SqlDbType.NVarChar,256);
            parameterRole.Value	                =   role;
            myCommand.Parameters.Add(parameterRole);

            try
            {
                myCommand.Connection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch ( Exception ex )
            {
                string msg                      =   string.Format("[E]CrmInstaller GrantAccess ex : {0}",ex);
                if ( DBTraceSwitch.Sw.TraceError )
                    Trace.WriteLine(msg,DBTraceSwitch.Sw.Info);
                throw;
            }
            finally
            {
                if ( myStaticConnection.State == System.Data.ConnectionState.Open )
                    myStaticConnection.Close();
            }

        }

        #endregion used by CrmInstaller


    }
}
